package JDBCUtil;

import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * Created by sunwei on 2017/9/26
 * Time:10:06
 */
public class JdbcUtil {

    public ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>();
    private static String userName;//表示定义的数据库的用户名
    private static String password;//表示定义的数据库的密码
    private static String driver; //定义的数据库的驱动信息
    private static String URL; //定义访问数据的地址
    private Connection connection;//定义数据库的链接
    private PreparedStatement pstmt;//定义sql语句的执行对象
    private ResultSet resultSet;//定义查询返回结果的结果集
    static {
        loadConfig();
    }
    //构造方法
    public JdbcUtil() {
        //加载数据库配置信息，并给相关的属性赋值
        loadConfig();
    }

    /*
    1.加载数据库配置信息，并给相关的属性赋值
     */
    public static void loadConfig(){
        try {
            InputStream inStream = JdbcUtil.class.getResourceAsStream("/resources/jdbc.properties");
            Properties prop = new Properties();
            prop.load(inStream);
            userName = prop.getProperty("jdbc.username");
            password = prop.getProperty("jdbc.password");
            driver = prop.getProperty("jdbc.driver");
            URL= prop.getProperty("jdbc.url");
        } catch (Exception e) {
            throw new RuntimeException("读取数据库配置文件异常",e);
        }
    }

    /*
    2.加载驱动和获取数据库连接，进行封装
    *@return 数据库连接
     */
    public  Connection getConnection(){
        try {
            Class.forName(driver);//注册驱动
            connection = DriverManager.getConnection(URL,userName,password);//获取连接
        } catch (Exception e) {
            throw new RuntimeException("获取连接失败"+e);
        }
        return connection;
    }
    public void setAutoCommit(Boolean b){
        try {
            connection.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     3.1执行增、删、改 操作
     * @param sql
     * @param params
     * @return 执行结果
     * @throw SQLException
     */
    public boolean updateByPreparedStatement(String sql, List<?> params, ResultSetHandler resultSetHandler, SetParams setParams)
            throws SQLException {
        boolean flag = false;
        int result = -1;//表示当用户执行增、删、和改的时候影响数据库的行数
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        //填充sql语句中的占位符
        if (params!=null&&!params.isEmpty()) {
            for(int i = 0; i <params.size() ; i++) {
                pstmt.setObject(index++,params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        flag = result>0 ? true:false;
        return flag;
    }

    /*
   * 执行查询操作
    */
    public List<Map<String,Object>> findResult(String sql, List<?> params)throws SQLException{
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params!=null&&!params.isEmpty()){
              for(int i = 0; i <params.size() ; i++) {
                  pstmt.setObject(index++,params.get(i));
              }
        }
        resultSet = pstmt.executeQuery();//执行数据库查询语句
        ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
        int cols_len = metaData.getColumnCount();//获取结果集的列数
        while (resultSet.next()){
            Map<String,Object> map = new HashMap<String,Object>();
            for(int i = 0; i <cols_len ; i++) {
                String cols_name = metaData.getColumnName(i+1);//获取结果集的列名（字段名）
                Object cols_value = resultSet.getObject(cols_name);//获取字段对应的value值
                if (cols_value==null) {
                      cols_value=" ";
                }
                map.put(cols_name,cols_value);
            }
            list.add(map);
        }
        return list;
    }


    /*
    4.释放资源
     */
    public void releaseConn(){
        if (resultSet!=null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt!=null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}
